# Locsi Control functions based on
# Locsi Database Interfacing functions and
# Google Calendar API v3 Resource representations and interfacing functions

#--------------------------------
# LocsiCTRL API
#--------------------------------
#
# Assumptions:  sqlite db file exists
#               LocsiDB has been initialized - LocsiDB.initdbfromGC()
#               LocsiDB.dbtable is 'Events'
#
# Global variables
#   LocsiCTRL.con - connection to LocsiDB, to be initialized with
#               LocsiCTRL.initLocsiCTRLdb(LocsiDB.dbpath) - get con
#               LocsiCTRL.initLocsiCTRLdb(LocsiDB.dbpath,1) - get con and create new LocsiCTRL table
#   LocsiCTRL.GCBacklog - counter of status updates attempted to be sent
#               to Google Calendar with no success
#
# API Functions
#   def initLocsiCTRLdb(dbpath = None, droptable = 0)
#       initialize LocsiCTRL.con global, used by all CTRL functions below
#       when droptable is 1 a new LocsiCTL table is created in LocsiDB.dbpath database
#
#   def loop()
#       starts the control loop which is executing events
#       - that are in the Events table - initialized by LocsiDB.initdbfromGC()
#       - selection of next event to execute is determined by loop type - see loopType() below
#       - execution state is controlled by the value of the 'status' field in LocsiCTRL table of LocsiDB
#           'Exit' - if status field is set to 'Exit' the control loop exits, execution is terminated, currently executed event is stopped
#           'Stopped' - execution of events is stopped, control loop continues running, waits for a change in status while event execution is paused
#           'Running' - executing events with selection and timing specified by loop type (see loopType())
#       - status is initialized to 'Exit' when LocsiCTRL table is created with initLocsiCTRLdb(dbpath, 1)
#       - to enable execution first set status to 'Running' with run()
#       - WARNING: - DOES NOT RETURN PROMPT IN A TERMINAL UNTIL EXIT
#       assumes initialized LocsiCTRL.con global
#
#   def run()
#       sets LocsiCTRL state to 'Running'
#       for executing events control loop must be running (see loop())
#       assumes initialized LocsiCTRL.con global
#
#   def stop()
#       sets LocsiCTRL state to 'Stopped'
#       assumes initialized LocsiCTRL.con global
#
#   def exitloop()
#       sets LocsiCTRL state to 'Exit'
#       assumes initialized LocsiCTRL.con global
#
#   def getstate()
#       sets LocsiCTRL state to 'Running'
#       assumes initialized LocsiCTRL.con global
#
#   def loopType( newLoopType = '' )
#       returns current LoopType with no arguments given
#       sets LoopType according the argument
#           REALTIME 
#               return first action not DONE yet to be started now or later
#               interrupted execution is not restarted,
#               in case of overlapping events some are left out
#           QUEUED
#               return first action that is not DONE yet
#               in the specified timewindow of the LocsiDB
#               all events executed
#               interrupted execution continued when restarted if still in the timewindow
#               start time is not guaranteed, started when the last preceding event is done
#       assumes initialized LocsiCTRL.con global
#

import LocsiHW
import LocsiDB
import sqlite3 as lite
import sys
import urllib
import time
import datetime

class Action:
    Start = None
    End = None
    Progress = 0
    TS_on = None
    TS_done = None
    Id = None
    SwitchId = None

    def getnextaction(self, now):
        global con, lastMsg

        if con == None:
            return

        con.row_factory = lite.Row
        cur = con.cursor()

        looptype = getfromLocsidb('LoopType')
        
        # REALTIME - LoopType
        # return first action not DONE yet to be started now or later
        # interrupted execution is not restarted,
        # in case of overlapping events some are left out
        firstpossiblestart = now

        # QUEUED - LoopType
        # return first action that is not DONE yet
        # in the specified timewindow of the LocsiDB
        # all events executed
        # interrupted execution continued when restarted if still in the timewindow
        # start time is not guaranteed, started when the last preceding event is done
        if looptype == 'QUEUED':
            firstpossiblestart = datetime.datetime(1,1,1,0,0,0,0)      
        
        self.Start = None
        self.End = None
        self.Progress = 0
        self.GCProgress = 0
        self.TS_on = None
        self.TS_done = None
        self.Id = None
        self.SwitchId = None

        try:

            cur.execute("SELECT * FROM %s "%LocsiDB.dbtable+" ORDER BY Start")
            rows = cur.fetchall()
            for row in rows:
                
                id = row["id"]
                #print "ID:",id

                eventStart = str(LocsiDB.decodefromdb(row["start"])).split('\'')[3]
                eventEnd = str(LocsiDB.decodefromdb(row["end"])).split('\'')[3]
                eventProgress = row["Progress"]
                eventDone = row["Done"]
                eventSwitchId = str(LocsiDB.decodefromdb(row["summary"])).split(' ')[0]
                
                dt_eventStart = datetime.datetime.strptime(eventStart[:-6],"%Y-%m-%dT%H:%M:%S")
                dt_eventEnd = datetime.datetime.strptime(eventEnd[:-6],"%Y-%m-%dT%H:%M:%S")
                dt_duration = dt_eventEnd - dt_eventStart

                #print 'EventStart:',dt_eventStart,dt_eventEnd,dt_duration

                # return first action in available that is not DONE yet according to the CTRL DB
                if (dt_eventStart >= firstpossiblestart) and (eventDone != 3):

                    # NEXT EVENT FOUND
                    if dt_eventStart < now:
                        dt_eventStart = now
                        dt_eventEnd = now + datetime.timedelta(seconds = dt_duration.seconds * (100-eventProgress)/100)

                    msg = str(id) + " Event found, start: "+str(dt_eventStart)+" end: "+str(dt_eventEnd)

                    if msg != lastMsg:
                        print now, msg
                        lastMsg = msg
                        
                    self.Id = id
                    self.Start = dt_eventStart
                    self.End = dt_eventEnd
                    self.Progress = eventProgress
                    self.initProgress = eventProgress
                    self.SwitchId = eventSwitchId
                    
                    return 1
                
                # alternative to the above to return the first action found which has a target Start time now or in the future
        except:
            msg = " Event not found. DB access problem."
            if msg != lastMsg:
                print now, msg
                lastMsg = msg 
            return 0
        
        msg = " Event not found."
        if msg != lastMsg:
            print now, msg
            lastMsg = msg 
        return 0

    def __init__(self, now):

        #self.Start = now + datetime.timedelta(0,1)
        #self.End = now + datetime.timedelta(0,5)

        self.getnextaction(now)

        print 'Action born', self.Id
        
    def on(self, now):
        global con, GCBacklog

        print now,' On:',self.SwitchId,self.Id

        LocsiHW.on(self.SwitchId)

        self.TS_on = now

        sqlupdate = 'UPDATE Events SET Progress = '+str(self.Progress)+', Done = "2" WHERE Id = "'+self.Id+'";'
        #print sqlupdate
        updateLocsidb(con, sqlupdate)

        # Update Google Calendar
        updatedsummary = self.SwitchId+' ON Since:'+str(now)+' Progress:'+str(self.Progress)+'%'
        try:
            service = LocsiDB.calapi.authenticategooglecalendarapi()
            event = LocsiDB.calapi.geteventfromcalendar(service,LocsiDB.decodefromdb
                                                        (self.Id))
            event['summary'] = updatedsummary
            event['colorId'] = '11'
            event = LocsiDB.calapi.updateeventincalendar(service, event)

            if GCBacklog > 0:
                updateGCwithBacklog(service,self.Id)
                GCBacklog = 0
     
        except:
            print now,": Problem updating GC - ", updatedsummary
            sqlupdate = 'UPDATE Events SET GCUpdate = "'+updatedsummary+'" WHERE Id = "'+self.Id+'";'
            #print sqlupdate
            updateLocsidb(con, sqlupdate)
            GCBacklog = GCBacklog + 1
        finally:
            print "Finally"    

    def stop(self, now):
        global con, GCBacklog

        self.updateprogress(now)
        
        print now,' Stop:',self.SwitchId,self.Id
        LocsiHW.off(self.SwitchId)

        sqlupdate = 'UPDATE Events SET Done = "1" WHERE Id = "'+self.Id+'";'
        #print sqlupdate
        updateLocsidb(con, sqlupdate)

        # Update Google Calendar
        updatedsummary = self.SwitchId+' STOPPED Since:'+str(now)+' Progress:'+str(self.Progress)+'%'
        try:
            service = LocsiDB.calapi.authenticategooglecalendarapi()
            event = LocsiDB.calapi.geteventfromcalendar(service,LocsiDB.decodefromdb
                                                        (self.Id))
            event['summary'] = updatedsummary
            event['colorId'] = '5'
            event = LocsiDB.calapi.updateeventincalendar(service, event)

            if GCBacklog > 0:
                updateGCwithBacklog(service,self.Id)
                GCBacklog = 0
     

        except:
            print now,": Problem updating GC - ", updatedsummary
            sqlupdate = 'UPDATE Events SET GCUpdate = "'+updatedsummary+'" WHERE Id = "'+self.Id+'";'
            #print sqlupdate
            updateLocsidb(con, sqlupdate)
            GCBacklog = GCBacklog + 1
        finally:
            print "Finally"    
    


    def updateprogress(self, now):
        global con, GCBacklog
        totalduration = self.End - self.Start
        duration = now - self.TS_on
        self.Progress = self.initProgress + (100-self.initProgress)*(duration.total_seconds()/totalduration.total_seconds())
        print now," Progress:",self.SwitchId,self.Id,self.Progress
        sqlupdate = 'UPDATE Events SET Progress = '+str(self.Progress)+', Done = "2" WHERE Id = "'+self.Id+'";'
        #print sqlupdate
        updateLocsidb(con, sqlupdate)

        if (self.Progress - self.GCProgress) > 25:
            self.GCProgress = self.Progress

            # Update Google Calendar
            updatedsummary = self.SwitchId+' ON Since:'+str(self.TS_on)+' Progress:'+str(self.Progress)+'%'
            try:
                service = LocsiDB.calapi.authenticategooglecalendarapi()
              
                event = LocsiDB.calapi.geteventfromcalendar(service,LocsiDB.decodefromdb
                                                            (self.Id))
                event['summary'] = updatedsummary
                event['colorId'] = '11'
                
                event = LocsiDB.calapi.updateeventincalendar(service, event)

                if GCBacklog > 0:
                    updateGCwithBacklog(service,self.Id)
                    GCBacklog = 0
         
            except:
                print now,": Problem updating GC - ", updatedsummary
                sqlupdate = 'UPDATE Events SET GCUpdate = "'+updatedsummary+'" WHERE Id = "'+self.Id+'";'
                #print sqlupdate
                updateLocsidb(con, sqlupdate)
                GCBacklog = GCBacklog + 1
            finally:
                print "Finally"
            

    def done(self, now):
        global con, GCBacklog
        print now,' Off: ',self.SwitchId,self.Id
        LocsiHW.off(self.SwitchId)
        
        self.Progress = 100
        sqlupdate = 'UPDATE Events SET Progress = '+str(self.Progress)+', Done = "3" WHERE Id = "'+self.Id+'";'
        #print sqlupdate
        updateLocsidb(con, sqlupdate)
        
        self.TS_done = now

        # Update Google Calendar
        updatedsummary = self.SwitchId+' DONE Since:'+str(now)+' Progress:100%'
        try:
            service = LocsiDB.calapi.authenticategooglecalendarapi()
            event = LocsiDB.calapi.geteventfromcalendar(service,LocsiDB.decodefromdb
                                                        (self.Id))
            event['summary'] = updatedsummary
            event['colorId'] = '9'
            event = LocsiDB.calapi.updateeventincalendar(service, event)

            if GCBacklog > 0:
                updateGCwithBacklog(service,self.Id)
                GCBacklog = 0

            if self.SwitchId == 'UPDATE':
                initdbfromGC(0,168)
                        
        except:
            print now,": Problem updating GC - ", updatedsummary
            sqlupdate = 'UPDATE Events SET GCUpdate = "'+updatedsummary+'" WHERE Id = "'+self.Id+'";'
            #print sqlupdate
            updateLocsidb(con, sqlupdate)
            GCBacklog = GCBacklog + 1
        finally:
            print "Finally"
            
def updateGCwithBacklog(service,selfId):
    global con, GCBacklog

    # SELECT Id, GCUpdate, Done FROM Events WHERE GCUpdate != ""
    #print "GC Backlog>0"
    con.row_factory = lite.Row
    cur = con.cursor()
    cur.execute("SELECT Id, GCUpdate, Done FROM %s "%LocsiDB.dbtable+" WHERE GCUpdate !='';")
    rows = cur.fetchall()
    for row in rows:
        id = row['id']
        
        if id != selfId:
            event = LocsiDB.calapi.geteventfromcalendar(service,LocsiDB.decodefromdb
                                                (id))
            event['summary'] = row['GCUpdate']
            # set event colour according to the value of Done
            done = row["Done"]                   
            if done == 1:
                #print "STOPPED 5"
                event['colorId'] = '5'
            elif done ==2:
                #print "ON 11"
                event['colorId'] = '11'
            elif done == 3:
                #print "DONE 9"
                event['colorId'] = '9'
                
            event = LocsiDB.calapi.updateeventincalendar(service, event)

    # UPDATE Events SET GCupdate = "" WHERE Id = ...
    cur.execute("UPDATE Events SET GCUpdate = '' WHERE GCUPdate !='';")
    con.commit()
        
def getfromLocsidb(column):
    global con
    
    state = None
    
    if con == None:
        return state
    
    try:
        con.row_factory = lite.Row
        cur = con.cursor()
        cur.execute('SELECT '+column+' FROM LocsiCTRL')
        state = cur.fetchone()
                         
    except lite.Error, e:

        print "Error connecting to DB %s:" %e.args[0]
        #sys.exit(1)
        raise

    finally:
        if con:
            return state[0]
        
def loopType( newLoopType = '' ):
    global con

    if newLoopType == '':
        return getfromLocsidb('LoopType')
    elif newLoopType == 'QUEUED' or newLoopType == 'REALTIME':
        sqlupdate = "UPDATE LocsiCTRL SET LoopType ='"+newLoopType+"';"
        updateLocsidb(con, sqlupdate)
        return 1
    return 0

def updateLocsidb(con, sqlupdate):
    try:
        cur = con.cursor()
       
        cur.execute(sqlupdate)
        
        con.commit()
                    
    except lite.Error, e:

        print "Error connecting to DB %s:" %e.args[0]
        #sys.exit(1)
        raise

    finally:
        if con:
            return con


def run():
    global con
    now = datetime.datetime.now()
       
    sqlupdate = 'UPDATE LocsiCTRL SET State = "Running", Since = "'+str(now)+'", LastHeartBeat = "'+str(now)+'";'

    updateLocsidb(con, sqlupdate)


def stop():
    global con
    now = datetime.datetime.now()

    sqlupdate = 'UPDATE LocsiCTRL SET State = "Stopped", Since = "'+str(now)+'", LastHeartBeat = "'+str(now)+'";'

    updateLocsidb(con, sqlupdate)

def exitloop():
    global con
    now = datetime.datetime.now()

    sqlupdate = 'UPDATE LocsiCTRL SET State = "Exit", Since = "'+str(now)+'", LastHeartBeat = "'+str(now)+'";'

    updateLocsidb(con, sqlupdate)

def sendheartbeat():
    global con    
    now = datetime.datetime.now()

    sqlupdate = 'UPDATE LocsiCTRL SET LastHeartBeat = "'+str(now)+'";'
    
    updateLocsidb(con, sqlupdate)


def getstate():
    return getfromLocsidb('state')
    
def loop():
    global con, GCBacklog

    prevstate = "Stopped"

    if con == None:
        print "Connection not initialized to LocsiCTRL database!\n"
        return

    now = datetime.datetime.now()
    #FOR SIMULATED TIME - set starting time
    #now =datetime.datetime(2013, 8, 25, 20, 59, 59, 103000)

    action = Action(now)

    state = getstate()

    while state <> "Exit":
        
        #print 'Start, End', action.Start, action.End
        
        if (action.Start != None) and (state == "Running"):
            if action.TS_on == None:
                if now >= action.Start:
                    # START ACTION
                    action.on(now)
                else:
                    action.getnextaction(now)
                    # Action(now)
            else:
                #print 'STARTED', action.TS_on
                if now >= action.End:
                    # END ACTION	
                    if action.TS_done == None:
                        action.done(now)	
                    action.getnextaction(now)
                    if action.Start != None:
                        if now >= action.Start:
                            # START ACTION
                            action.on(now)
                else:
                    # Progress
                    #print 'PROGRESS'
                    action.updateprogress(now)

        else:
            if prevstate <> "Stopped":
                # STOP ACTION
                if action.TS_on != None:
                    action.stop(now)
            #print 'CHECK FOR AN ACTION'
            action.getnextaction(now)
                    
                    
        time.sleep(5)
        
        now = datetime.datetime.now()
        #FOR SIMULATED TIME - one tick 10 seconds:
        #now = now + datetime.timedelta(seconds=10)
        
        sendheartbeat()
        prevstate = state
        state = getstate()
        if (state == 'Exit') and (action.TS_on != None):
            action.stop(now)
        #print state,now

def initLocsiCTRLdb(dbpath = None, droptable = 0):
    global con
    
    con = None

    if dbpath == None:
        dbpath = LocsiDB.dbpath

    try:
        con = lite.connect(dbpath)
        cur = con.cursor()

        if droptable == 1 :
            cur.execute("DROP TABLE IF EXISTS LocsiCTRL;" )
            cur.execute("CREATE TABLE LocsiCTRL ( Id INTEGER PRIMARY KEY, State TXT, Since TXT, LastHeartBeat TXT, LoopType TXT );")
            print "Old LocsiCTRL dropped, new initialized."
            
            now = datetime.datetime.now()
       
            #sqlupdate = 'UPDATE LocsiCTRL SET State = "Running", Since = "'+str(now)+'", LastHeartBeat = "'+str(now)+'";'
            #sqlupdate = 'UPDATE LocsiCTRL SET State = "Stopped", Since = "'+str(now)+'", LastHeartBeat = "'+str(now)+'";'
            #sqlupdate = 'UPDATE LocsiCTRL SET LastHeartBeat = "'+str(now)+'";'
        
            sqlinsert = 'INSERT INTO LocsiCTRL VALUES(1, "Exit","'+str(now)+'","'+str(now)+'","QUEUED");'
            print "SQL INSERT: %s \n" %sqlinsert
            cur.execute(sqlinsert)
        
            con.commit()
                    
    except lite.Error, e:

        print "Error connecting to DB %s:" %e.args[0]
        #sys.exit(1)
        raise

    finally:
        if con:
            return con

def initdbfromGC(hoursm = 0, hoursp = 168, ldbpath=None, ldbtable=None):
    global con

    if ldbpath != None:
        LocsiDB.dbpath = ldbpath

    if ldbtable != None:
        LocsiDB.dbtable = ldbtable

    print "Locsi DB path: %s" %LocsiDB.dbpath
    print "Locsi DB table: %s" %LocsiDB.dbtable

    try:
        service = LocsiDB.calapi.authenticategooglecalendarapi()
        # events = calapi.geteventsfromcalendar( service, 'primary' )

        events = LocsiDB.calapi.geteventsfromcalendarintimewindow( service, 'primary', hoursm, hoursp )

        con = LocsiDB.initdb(LocsiDB.dbpath, LocsiDB.dbtable,1)
        cur = con.cursor()
        
        #cur.execute('SELECT SQLITE_VERSION()')
        #data = cur.fetchone()
        # print "SQLite version: %s" % data
        #print events
        #print type(events)

        #print "GENERATE INSERT"
        for event in events:
            updatedsummary = event['summary'].split(' ')[0] + ' DOWNLOADED'
            event['colorId'] = '6'
            event['summary'] = updatedsummary
            sqlinsert1 = 'INSERT INTO %s(Progress, Touched, Done, GCUpdate, ' %LocsiDB.dbtable
            sqlinsert2 = "VALUES (0, 0, 0, '',"
            for c, v in event.iteritems():
                sqlinsert1 = sqlinsert1 + c + ', '
                sqlinsert2 = sqlinsert2 + "'"+ LocsiDB.encodefordb(v)+"'" + ', '

            sqlinsert = sqlinsert1[:-2] + ') ' + sqlinsert2[:-2] + ');'

            #print sqlinsert

            cur.execute( sqlinsert )

            event = LocsiDB.calapi.updateeventincalendar(service, event)

        con.commit()

        print "Locsi DB Initialized X"
        return con
    except:
        print "Locsi DB Init from GC Unsuccesful!"
        return con
    
con = None
GCBacklog = 0
lastMsg = ''

#con = initLocsiCTRLdb(LocsiDB.dbpath,1)
#print getstate()
#run()
#print getstate()
#stop()
#print getstate()
#exitloop()
#print getstate()

#LocsiCTRL.getstate()
#LocsiCTRL.run()
#LocsiCTRL.getstate()
#LocsiCTRL.stop()
#LocsiCTRL.getstate()
#LocsiCTRL.exitloop()
#LocsiCTRL.getstate()

"""
import LocsiCTRL
import LocsiDB

LocsiDB.initdbfromGC()
LocsiCTRL.initLocsiCTRLdb(LocsiDB.dbpath)
LocsiCTRL.run()
LocsiCTRL.loop()

reload(LocsiCTRL)

LocsiCTRL.initLocsiCTRLdb(LocsiDB.dbpath)
LocsiCTRL.run()
LocsiCTRL.loop()

sqlite console:

SELECT Id, Progress, Done FROM Events;
SELECT Id, Progress, Done FROM Events;

UPDATE LocsiCTRL SET State = "Stop";

SELECT Id, Progress, Done FROM Events;
SELECT Id, Progress, Done FROM Events;

UPDATE LocsiCTRL SET State = "Running";

SELECT Id, Progress, Done FROM Events;
SELECT Id, Progress, Done FROM Events;

UPDATE LocsiCTRL SET State = "Exit";
"""
